Importando as bibliotecas necessárias

library(tidyverse)
library(readxl)
library(plotly)
library(gmodels)
library(sqldf)

Carregando o arquivo Cadastral para fazer as análises

ds <- read_excel("cadastral.xlsx")
ds
  1. Tire uma tabela de frequência usando a função table na variável Sexo. Quantos homens e quantas mulheres têm no arquivo?
table(ds$Sexo)

 Feminino Masculino 
      432       514 
  1. Ordenar a variável ID.
ds <- ds[order(ds$ID),]
ds
  1. Remova os ID duplicados. Coloque esse arquivo dentro de um objeto chamado A.
A <- unique(ds)
A
  1. Já no objeto A. Tire uma tabela de frequência usando a função table na variável Sexo. Quantos homens e quantas mulheres têm no arquivo?
table(A$Sexo)

 Feminino Masculino 
      216       257 
  1. Crie uma variável data atual e acrescenta essa variável ao objeto/ arquivo A.
A$data_atual <- Sys.Date()
A
  1. Verifique se a variável salario é numérica?
is.numeric(A$salario)
[1] TRUE
  1. Mostre o mínimo e o máximo da variável salario.
print(sprintf("Mínimo salário -> %0.2f", min(A$salario)))
[1] "Mínimo salário -> 1575.00"
print(sprintf("Máximo salário -> %0.2f", max(A$salario)))
[1] "Máximo salário -> 13500.00"
  1. Crie uma variável faixa de salario com as seguintes quebras: 1574, 3000, 5000, 7000, 13500.
A$faixa_salario <- cut(A$salario, c(1574, 3000, 5000, 7000, 13500),label=c("A","B","C","D"))
A
  1. Crie um visualizador/ matriz usando a função View(A). Exatamente esse comando.
View(A)
  1. Atribua o arquivo Transacional ao objeto B. E crie um visualizador/ matriz usando a função View(B).
B <- read_excel("transacional.xlsx")
View(B)
  1. Crie um objeto chamado consolidado e faça uma união dos arquivos A e B através do Left join. Usando a função do R.
consolidado <- merge(A,B, by='ID', all.x=T)
consolidado
  1. Crie uma variável comprometimento de renda usando as variáveis ValorEmprestimo e Salario. Para isso utilize a expressão. (ValorEmprestimo / salario). Quantas variáveis ficaram no arquivo?
consolidado$comprometido_de_renda <- (consolidado$ValorEmprestimo / consolidado$salario)
print(sprintf("Número de colunas -> %0.0f", ncol(consolidado)))
[1] "Número de colunas -> 18"
  1. Faça um gráfico de pie usando o pacote plotly. Pode usar qualquer variáveis Explique o gráfico.
fig <- plot_ly(consolidado, labels = ~faixa_salario, values = ~salario, type = 'pie')
fig <- fig %>% layout(title = 'Faixa salarial',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

fig
#Criei um gráfico de pizza com a variável de faixa salarial:
#      39,2% das pessoas pertecem ao grupo A ([1500 - 3000[)
#      30,7% das pessoas pertecem ao grupo B ([3000 - 5000[)
#      17,7% das pessoas pertecem ao grupo C ([5000 - 7000[)
#      12,4% das pessoas pertecem ao grupo D ([7000 - 13500[)
# Temos praticamente 70% das pessoas com renda salarial de até 5000,00 reais formados pela class A e # B
  1. Faça um tabela cruzada usando a função Crosstable do packege gmodels das variáveis sexo e default. Olhando para percentual coluna, quem tem o maior % de inadimplência H ou M? e qual é esse valor.
Cross <- CrossTable(consolidado$Sexo, consolidado$default)

 
   Cell Contents
|-------------------------|
|                       N |
| Chi-square contribution |
|           N / Row Total |
|           N / Col Total |
|         N / Table Total |
|-------------------------|

 
Total Observations in Table:  473 

 
                 | consolidado$default 
consolidado$Sexo |   Adimplente | Inadimplente |    Row Total | 
-----------------|--------------|--------------|--------------|
        Feminino |           49 |          167 |          216 | 
                 |        1.810 |        0.686 |              | 
                 |        0.227 |        0.773 |        0.457 | 
                 |        0.377 |        0.487 |              | 
                 |        0.104 |        0.353 |              | 
-----------------|--------------|--------------|--------------|
       Masculino |           81 |          176 |          257 | 
                 |        1.521 |        0.577 |              | 
                 |        0.315 |        0.685 |        0.543 | 
                 |        0.623 |        0.513 |              | 
                 |        0.171 |        0.372 |              | 
-----------------|--------------|--------------|--------------|
    Column Total |          130 |          343 |          473 | 
                 |        0.275 |        0.725 |              | 
-----------------|--------------|--------------|--------------|

 
print("Quem tem o maior precentual de inadiplência são os homens com 51,30%")
[1] "Quem tem o maior precentual de inadiplência são os homens com 51,30%"
  1. Utilizando a biblioteca SQLDF pede-se:
    1. Selecione da tabela consolidado os registros do sexo masculino e que possui um conta particular.
    2. Tira a média de QtdaPagas pela variável Atraso .
sexo_masculino_particular <- sqldf("select * from consolidado where sexo = 'Masculino' and conta = 'Particular'")
sexo_masculino_particular
QtdaPagas_vs_Atraso <- sqldf("select Atraso, avg(QtdaPagas) as media_QtdaPagas from consolidado group by Atraso")
QtdaPagas_vs_Atraso
  1. Calcule a expressão numérica: (((√16)/2)3^2)/2(9-2^3),
calculo1 = ((sqrt(16)/2)*3^2)/2*(9-2^3)
calculo1
[1] 9
  1. Calcule a expressão numérica: -(-23)+(-〖1)〗0-(√(25−3^2 ))-5^3/25
calculo2 <- -(-2^3) + (-1)^0 - sqrt(25 - ( 3 ^ 2)) - ( (5 ^ 3) / 25 )
calculo2
[1] 0
LS0tDQp0aXRsZTogIlRyYWJhbGhvIGRlIFIgLSBGSUFQIg0Kb3V0cHV0OiBodG1sX25vdGVib29rDQotLS0NCg0KSW1wb3J0YW5kbyBhcyBiaWJsaW90ZWNhcyBuZWNlc3PDoXJpYXMNCg0KYGBge3J9DQpsaWJyYXJ5KHRpZHl2ZXJzZSkNCmxpYnJhcnkocmVhZHhsKQ0KbGlicmFyeShwbG90bHkpDQpsaWJyYXJ5KGdtb2RlbHMpDQpsaWJyYXJ5KHNxbGRmKQ0KYGBgDQoNCkNhcnJlZ2FuZG8gbyBhcnF1aXZvIENhZGFzdHJhbCBwYXJhIGZhemVyIGFzIGFuw6FsaXNlcw0KDQpgYGB7cn0NCmRzIDwtIHJlYWRfZXhjZWwoImNhZGFzdHJhbC54bHN4IikNCmRzDQpgYGANCg0KMSkgVGlyZSB1bWEgdGFiZWxhIGRlIGZyZXF1w6puY2lhIHVzYW5kbyBhIGZ1bsOnw6NvIHRhYmxlIG5hIHZhcmnDoXZlbCBTZXhvLiBRdWFudG9zIGhvbWVucyBlIHF1YW50YXMgbXVsaGVyZXMgdMOqbSBubyBhcnF1aXZvPw0KDQpgYGB7cn0NCnRhYmxlKGRzJFNleG8pDQpgYGANCjIpIE9yZGVuYXIgYSB2YXJpw6F2ZWwgSUQuDQoNCmBgYHtyfQ0KZHMgPC0gZHNbb3JkZXIoZHMkSUQpLF0NCmRzDQpgYGANCg0KMykgUmVtb3ZhIG9zIElEIGR1cGxpY2Fkb3MuIENvbG9xdWUgZXNzZSBhcnF1aXZvIGRlbnRybyBkZSB1bSBvYmpldG8gY2hhbWFkbyBBLg0KDQpgYGB7cn0NCkEgPC0gdW5pcXVlKGRzKQ0KQQ0KYGBgDQoNCjQpIErDoSBubyBvYmpldG8gQS4gVGlyZSB1bWEgdGFiZWxhIGRlIGZyZXF1w6puY2lhIHVzYW5kbyBhIGZ1bsOnw6NvIHRhYmxlIG5hIHZhcmnDoXZlbCBTZXhvLiBRdWFudG9zIGhvbWVucyBlIHF1YW50YXMgbXVsaGVyZXMgdMOqbSBubyBhcnF1aXZvPw0KDQpgYGB7cn0NCnRhYmxlKEEkU2V4bykNCmBgYA0KNSkgQ3JpZSB1bWEgdmFyacOhdmVsIGRhdGEgYXR1YWwgZSBhY3Jlc2NlbnRhIGVzc2EgdmFyacOhdmVsIGFvIG9iamV0by8gYXJxdWl2byBBLg0KDQpgYGB7cn0NCkEkZGF0YV9hdHVhbCA8LSBTeXMuRGF0ZSgpDQpBDQpgYGANCjYpIFZlcmlmaXF1ZSBzZSBhIHZhcmnDoXZlbCBzYWxhcmlvIMOpIG51bcOpcmljYT8NCg0KYGBge3J9DQppcy5udW1lcmljKEEkc2FsYXJpbykNCmBgYA0KNykgTW9zdHJlIG8gbcOtbmltbyBlIG8gbcOheGltbyBkYSB2YXJpw6F2ZWwgc2FsYXJpby4NCg0KYGBge3J9DQpwcmludChzcHJpbnRmKCJNw61uaW1vIHNhbMOhcmlvIC0+ICUwLjJmIiwgbWluKEEkc2FsYXJpbykpKQ0KcHJpbnQoc3ByaW50ZigiTcOheGltbyBzYWzDoXJpbyAtPiAlMC4yZiIsIG1heChBJHNhbGFyaW8pKSkNCmBgYA0KDQo4KSBDcmllIHVtYSB2YXJpw6F2ZWwgZmFpeGEgZGUgc2FsYXJpbyBjb20gYXMgc2VndWludGVzIHF1ZWJyYXM6IDE1NzQsIDMwMDAsIDUwMDAsIDcwMDAsIDEzNTAwLg0KDQpgYGB7cn0NCkEkZmFpeGFfc2FsYXJpbyA8LSBjdXQoQSRzYWxhcmlvLCBjKDE1NzQsIDMwMDAsIDUwMDAsIDcwMDAsIDEzNTAwKSxsYWJlbD1jKCJBIiwiQiIsIkMiLCJEIikpDQpBDQpgYGANCjkpIENyaWUgdW0gdmlzdWFsaXphZG9yLyBtYXRyaXogdXNhbmRvIGEgZnVuw6fDo28gVmlldyhBKS4gRXhhdGFtZW50ZSBlc3NlIGNvbWFuZG8uDQpgYGB7cn0NClZpZXcoQSkNCmBgYA0KDQoxMCkgQXRyaWJ1YSBvIGFycXVpdm8gVHJhbnNhY2lvbmFsIGFvIG9iamV0byBCLiBFIGNyaWUgdW0gdmlzdWFsaXphZG9yLyBtYXRyaXogdXNhbmRvIGEgZnVuw6fDo28gVmlldyhCKS4NCg0KYGBge3J9DQpCIDwtIHJlYWRfZXhjZWwoInRyYW5zYWNpb25hbC54bHN4IikNClZpZXcoQikNCmBgYA0KDQoxMSkgQ3JpZSB1bSBvYmpldG8gY2hhbWFkbyBjb25zb2xpZGFkbyBlIGZhw6dhIHVtYSB1bmnDo28gZG9zIGFycXVpdm9zIEEgZSBCIGF0cmF2w6lzIGRvIExlZnQgam9pbi4gVXNhbmRvIGEgZnVuw6fDo28gZG8gUi4NCg0KYGBge3J9DQpjb25zb2xpZGFkbyA8LSBtZXJnZShBLEIsIGJ5PSdJRCcsIGFsbC54PVQpDQpjb25zb2xpZGFkbw0KYGBgDQoxMikgQ3JpZSB1bWEgdmFyacOhdmVsIGNvbXByb21ldGltZW50byBkZSByZW5kYSB1c2FuZG8gYXMgdmFyacOhdmVpcyBWYWxvckVtcHJlc3RpbW8gZSBTYWxhcmlvLiAgUGFyYSBpc3NvIHV0aWxpemUgYSBleHByZXNzw6NvLiAoVmFsb3JFbXByZXN0aW1vIC8gc2FsYXJpbykuIFF1YW50YXMgdmFyacOhdmVpcyBmaWNhcmFtIG5vIGFycXVpdm8/DQoNCmBgYHtyfQ0KY29uc29saWRhZG8kY29tcHJvbWV0aWRvX2RlX3JlbmRhIDwtIChjb25zb2xpZGFkbyRWYWxvckVtcHJlc3RpbW8gLyBjb25zb2xpZGFkbyRzYWxhcmlvKQ0KcHJpbnQoc3ByaW50ZigiTsO6bWVybyBkZSBjb2x1bmFzIC0+ICUwLjBmIiwgbmNvbChjb25zb2xpZGFkbykpKQ0KYGBgDQoNCg0KDQoNCg0KDQoNCg0KMTMpIEZhw6dhIHVtIGdyw6FmaWNvIGRlIHBpZSB1c2FuZG8gbyBwYWNvdGUgcGxvdGx5LiBQb2RlIHVzYXIgcXVhbHF1ZXIgdmFyacOhdmVpcyBFeHBsaXF1ZSBvIGdyw6FmaWNvLg0KDQoNCmBgYHtyfQ0KZmlnIDwtIHBsb3RfbHkoY29uc29saWRhZG8sIGxhYmVscyA9IH5mYWl4YV9zYWxhcmlvLCB2YWx1ZXMgPSB+c2FsYXJpbywgdHlwZSA9ICdwaWUnKQ0KZmlnIDwtIGZpZyAlPiUgbGF5b3V0KHRpdGxlID0gJ0ZhaXhhIHNhbGFyaWFsJywNCiAgICAgICAgIHhheGlzID0gbGlzdChzaG93Z3JpZCA9IEZBTFNFLCB6ZXJvbGluZSA9IEZBTFNFLCBzaG93dGlja2xhYmVscyA9IEZBTFNFKSwNCiAgICAgICAgIHlheGlzID0gbGlzdChzaG93Z3JpZCA9IEZBTFNFLCB6ZXJvbGluZSA9IEZBTFNFLCBzaG93dGlja2xhYmVscyA9IEZBTFNFKSkNCg0KZmlnDQpgYGANCmBgYHtyfQ0KI0NyaWVpIHVtIGdyw6FmaWNvIGRlIHBpenphIGNvbSBhIHZhcmnDoXZlbCBkZSBmYWl4YSBzYWxhcmlhbDoNCiMgICAgICAzOSwyJSBkYXMgcGVzc29hcyBwZXJ0ZWNlbSBhbyBncnVwbyBBIChbMTUwMCAtIDMwMDBbKQ0KIyAgICAgIDMwLDclIGRhcyBwZXNzb2FzIHBlcnRlY2VtIGFvIGdydXBvIEIgKFszMDAwIC0gNTAwMFspDQojICAgICAgMTcsNyUgZGFzIHBlc3NvYXMgcGVydGVjZW0gYW8gZ3J1cG8gQyAoWzUwMDAgLSA3MDAwWykNCiMgICAgICAxMiw0JSBkYXMgcGVzc29hcyBwZXJ0ZWNlbSBhbyBncnVwbyBEIChbNzAwMCAtIDEzNTAwWykNCiMgVGVtb3MgcHJhdGljYW1lbnRlIDcwJSBkYXMgcGVzc29hcyBjb20gcmVuZGEgc2FsYXJpYWwgZGUgYXTDqSA1MDAwLDAwIHJlYWlzIGZvcm1hZG9zIHBlbGEgY2xhc3MgQSBlICMgQg0KYGBgDQoNCjE0KSBGYcOnYSB1bSAgdGFiZWxhIGNydXphZGEgdXNhbmRvIGEgZnVuw6fDo28gQ3Jvc3N0YWJsZSBkbyBwYWNrZWdlIGdtb2RlbHMgZGFzIHZhcmnDoXZlaXMgc2V4byBlIGRlZmF1bHQuIE9saGFuZG8gcGFyYSBwZXJjZW50dWFsIGNvbHVuYSwgcXVlbSB0ZW0gbyBtYWlvciAlIGRlIGluYWRpbXBsw6puY2lhIEggb3UgTT8gZSBxdWFsIMOpIGVzc2UgdmFsb3IuDQoNCmBgYHtyfQ0KQ3Jvc3MgPC0gQ3Jvc3NUYWJsZShjb25zb2xpZGFkbyRTZXhvLCBjb25zb2xpZGFkbyRkZWZhdWx0KQ0KYGBgDQpgYGB7cn0NCnByaW50KCJRdWVtIHRlbSBvIG1haW9yIHByZWNlbnR1YWwgZGUgaW5hZGlwbMOqbmNpYSBzw6NvIG9zIGhvbWVucyBjb20gNTEsMzAlIikNCmBgYA0KMTUpIFV0aWxpemFuZG8gYSBiaWJsaW90ZWNhIFNRTERGIHBlZGUtc2U6DQogICAgICAgYSkgU2VsZWNpb25lIGRhIHRhYmVsYSBjb25zb2xpZGFkbyBvcyByZWdpc3Ryb3MgZG8gc2V4byBtYXNjdWxpbm8gZSBxdWUgcG9zc3VpIHVtIGNvbnRhIHBhcnRpY3VsYXIuDQogICAgICAgYikgVGlyYSBhIG3DqWRpYSBkZSBRdGRhUGFnYXMgcGVsYSB2YXJpw6F2ZWwgQXRyYXNvIC4NCg0KYGBge3J9DQpzZXhvX21hc2N1bGlub19wYXJ0aWN1bGFyIDwtIHNxbGRmKCJzZWxlY3QgKiBmcm9tIGNvbnNvbGlkYWRvIHdoZXJlIHNleG8gPSAnTWFzY3VsaW5vJyBhbmQgY29udGEgPSAnUGFydGljdWxhciciKQ0Kc2V4b19tYXNjdWxpbm9fcGFydGljdWxhcg0KYGBgDQpgYGB7cn0NClF0ZGFQYWdhc192c19BdHJhc28gPC0gc3FsZGYoInNlbGVjdCBBdHJhc28sIGF2ZyhRdGRhUGFnYXMpIGFzIG1lZGlhX1F0ZGFQYWdhcyBmcm9tIGNvbnNvbGlkYWRvIGdyb3VwIGJ5IEF0cmFzbyIpDQpRdGRhUGFnYXNfdnNfQXRyYXNvDQpgYGAgICAgICANCg0KMTYpIENhbGN1bGUgYSBleHByZXNzw6NvIG51bcOpcmljYToNCgkoKCjiiJoxNikvMikqM14yKS8yKig5LTJeMyksDQoJDQpgYGB7cn0NCmNhbGN1bG8xID0gKChzcXJ0KDE2KS8yKSozXjIpLzIqKDktMl4zKQ0KY2FsY3VsbzENCmBgYA0KMTcpIENhbGN1bGUgYSBleHByZXNzw6NvIG51bcOpcmljYToNCgktKC0yXjMpKygt44CWMSnjgJdeMC0o4oiaKDI14oiSM14yICkpLTVeMy8yNQ0KDQpgYGB7cn0NCmNhbGN1bG8yIDwtIC0oLTJeMykgKyAoLTEpXjAgLSBzcXJ0KDI1IC0gKCAzIF4gMikpIC0gKCAoNSBeIDMpIC8gMjUgKQ0KY2FsY3VsbzINCmBgYA0KDQo=